package uit.shoeshop.dao;

import uit.shoeshop.dto.BillDetailDTO;
import uit.shoeshop.dto.BillInfoDTO;
import uit.shoeshop.dto.ProductDTO;
import uit.shoeshop.lib.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import com.mysql.jdbc.Statement;

public class BillDAO {
	private Connection conn;
	private String tableBillInfo;
	private String tableBillDetail;
	
	public BillDAO(){
		setConn(null);
		setTableBillDetail(Constants.TBL_BILL_DETAIL);
		setTableBillInfo(Constants.TBL_BILL_INFO);

	}

	public ArrayList<BillInfoDTO> getAllBill(int page) throws SQLException{
		ArrayList<BillInfoDTO> results = new ArrayList<BillInfoDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableBillInfo() + " ORDER BY bill_id DESC LIMIT ?,?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, Constants.LIMIT_BILL*(page-1));
			stm.setInt(2, Constants.LIMIT_BILL);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				BillInfoDTO bill = new BillInfoDTO();
				bill.setBill_id(rs.getInt("bill_id"));
				bill.setFull_name(rs.getString("full_name"));
				bill.setCompany(rs.getString("company"));
				bill.setPhone(rs.getInt("phone"));
				bill.setAccepted(rs.getInt("accepted"));
				results.add(bill);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public int countTotalBill() throws SQLException{
		int results = 0;
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT count(*) as total_bill FROM " + getTableBillInfo() + " ORDER BY bill_id DESC";
			PreparedStatement stm = getConn().prepareStatement(sql);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results = rs.getInt("total_bill");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public BillInfoDTO getBillbyID(int sId) throws SQLException{
		BillInfoDTO results = new BillInfoDTO();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableBillInfo() + " WHERE bill_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, sId);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results.setBill_id(rs.getInt("bill_id"));
				results.setFull_name(rs.getString("full_name"));
				results.setCompany(rs.getString("company"));
				results.setAddress(rs.getString("address"));
				
				sql = "SELECT * FROM " + Constants.TBL_CITY + " WHERE city_id = ?";
				stm = getConn().prepareStatement(sql);
				stm.setInt(1, rs.getInt("city_id"));
				//thuc hien cau lenh sql
				ResultSet rst = stm.executeQuery();
				while (rst.next()) {
					results.setCity_name(rst.getString("name"));
				}
				
				results.setPhone(rs.getInt("phone"));
				results.setComment(rs.getString("comment"));
				results.setAccepted(rs.getInt("accepted"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public ArrayList<BillDetailDTO> getAllBillDetail(int sId) throws SQLException{
		ArrayList<BillDetailDTO> results = new ArrayList<BillDetailDTO>();
		
		try{
			setConn(Util.Connect());
			String sql = "SELECT * FROM " + getTableBillDetail() + " as b left join "+Constants.TBL_PRODUCT+" as p ON(b.product_id = p.product_id) WHERE b.bill_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, sId);
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				BillDetailDTO bill = new BillDetailDTO();
				bill.setBill_id(rs.getInt("b.bill_id"));
				bill.setPrice(rs.getInt("b.price")/rs.getInt("qty"));
				bill.setProduct_id(rs.getInt("b.product_id"));
				bill.setProduct_image(rs.getString("p.image_main"));
				bill.setProduct_name(rs.getString("p.title"));
				bill.setQty(rs.getInt("qty"));
				results.add(bill);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	
	public int delBill(int sId) throws SQLException{
		int result = 1;
		try{
			setConn(Util.Connect());
			String sql = "DELETE FROM " +getTableBillDetail() +" WHERE bill_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, sId);
			stm.executeUpdate();
			sql = "DELETE FROM " +getTableBillInfo() +" WHERE bill_id = ?";
			stm = getConn().prepareStatement(sql);
			stm.setInt(1, sId);
			stm.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			result = 0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}		
		return result;
	}	
	
	public int editBill(int sId,int accepted) throws SQLException{
		int result = 1;
		try{
			setConn(Util.Connect());
			
			String sql = "UPDATE " +getTableBillInfo() +" SET accepted = ? WHERE bill_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			stm.setInt(1, accepted);
			stm.setInt(2, sId);
			stm.executeUpdate();
			
			if(accepted==3){
				sql = "SELECT * FROM " + getTableBillDetail() + " WHERE bill_id = ?";
				stm = getConn().prepareStatement(sql);
				stm.setInt(1, sId);
				//thuc hien cau lenh sql
				ResultSet rs = stm.executeQuery();
				while (rs.next()) {
					sql = "UPDATE " +Constants.TBL_PRODUCT +" SET in_stock=in_stock+? WHERE product_id = ?";
					stm = getConn().prepareStatement(sql);
					stm.setInt(1, rs.getInt("qty"));
					stm.setInt(2, rs.getInt("product_id"));
					stm.executeUpdate();
				}
			}
		}catch(Exception e){
			e.printStackTrace();
			result = 0;
		}finally{
			if(getConn() != null)
				getConn().close();
		}		
		return result;
	}	
	
	public int insertBill(HashMap<Integer,Integer> cart,HashMap<Integer,ProductDTO> products,String company,String fullname,String address,String phone,String city,String comment ) throws SQLException{
		int result = 1;
		ResultSet generatedKeys = null;
		try{
			setConn(Util.Connect());
			getConn().setAutoCommit(false);
			String sql = "INSERT INTO "+getTableBillInfo()+" (company,full_name,address,city_id,phone,comment) VALUES (?,?,?,?,?,?)";
			PreparedStatement stmBillInfo = getConn().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			if(company == null) 
				stmBillInfo.setString(1, "");
			else
				stmBillInfo.setString(1, company);

			stmBillInfo.setString(2, fullname);
			stmBillInfo.setString(3, address);			
			stmBillInfo.setString(4, city);			
			stmBillInfo.setString(5, phone);
			if(comment == null) 
				stmBillInfo.setString(6, "");
			else
				stmBillInfo.setString(6, comment);			
			
			int check = stmBillInfo.executeUpdate();
			if (check < 1) {
				throw new Exception("Khong thuc hien duoc insert.");
	        }		
			generatedKeys = stmBillInfo.getGeneratedKeys();
			int billId = -1;
			if (generatedKeys.next()) {
				billId = generatedKeys.getInt(1);
	        } else {
	            throw new Exception("Khong get duoc last id.");
	        } 
			
			ProductDTO product;
			Set set = cart.entrySet();
	    	Iterator i = set.iterator();
	    	sql = "INSERT INTO "+getTableBillDetail()+" (bill_id,product_id,qty,price) VALUES (?,?,?,?)";
	    	String sql2 = "UPDATE "+Constants.TBL_PRODUCT+" SET in_stock=in_stock-? WHERE product_id=? and in_stock-? >= 0";
	    	while(i.hasNext()) {
	        	Map.Entry me = (Map.Entry)i.next();
	        	Integer key = Integer.parseInt(me.getKey().toString());
	        	Integer value = Integer.parseInt(me.getValue().toString());
	        	product = products.get(key);
	        	PreparedStatement stmBillDetail = getConn().prepareStatement(sql);
	        	PreparedStatement stmProduct = getConn().prepareStatement(sql2);
	        	stmBillDetail.setInt(1, billId);
	        	stmBillDetail.setInt(2, key);
	        	stmBillDetail.setInt(3, value);
	        	stmBillDetail.setInt(4, product.getPrice()*value);
	        	stmBillDetail.execute();
	        	
	        	stmProduct.setInt(1, value);
	        	stmProduct.setInt(2, key);
	        	stmProduct.setInt(3, value);
	        	if(stmProduct.executeUpdate() < 1)
	        		throw new Exception("Khong du san pham.");
	    	}			
			getConn().commit();
			getConn().setAutoCommit(true);
		}catch(Exception e){
			e.printStackTrace();			
			result = -1;
		}finally{
			if(getConn() != null)
				getConn().close();
			if(generatedKeys != null)
				generatedKeys.close();
		}		
		return result;
	}

	public Integer checkBillDetail(int id) throws SQLException{
		Integer results = 0;		
		try{
			setConn(Util.Connect());
			String sql = "SELECT count(*) as total_bill FROM " + getTableBillDetail() + " WHERE product_id = ?";
			PreparedStatement stm = getConn().prepareStatement(sql);
			
			stm.setInt(1, id);
			
			//thuc hien cau lenh sql
			ResultSet rs = stm.executeQuery();
			
			while (rs.next()) {
				results = rs.getInt("total_bill");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(getConn() != null)
				getConn().close();
		}
		return results;
	}
	/**
	 * @param conn the conn to set
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	/**
	 * @return the conn
	 */
	public Connection getConn() {
		return conn;
	}

	/**
	 * @param tableBillInfo the tableBillInfo to set
	 */
	public void setTableBillInfo(String tableBillInfo) {
		this.tableBillInfo = tableBillInfo;
	}

	/**
	 * @return the tableBillInfo
	 */
	public String getTableBillInfo() {
		return tableBillInfo;
	}

	/**
	 * @param tableBillDetail the tableBillDetail to set
	 */
	public void setTableBillDetail(String tableBillDetail) {
		this.tableBillDetail = tableBillDetail;
	}

	/**
	 * @return the tableBillDetail
	 */
	public String getTableBillDetail() {
		return tableBillDetail;
	}
}
